package cn.icanci.batch;

import java.sql.*;

/**
 * @Author: icanci
 * @ProjectName: kangaroo-orm
 * @PackageName: cn.icanci.batch
 * @Date: Created in 2020/8/31 21:35
 * @ClassAction: 测试批量插入
 */
public class TestBach {
    private static Connection conn = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;
    // 数据库地址
    private static final String URL = "jdbc:mysql://localhost:3306/kangaroo-orm?useSSL=false&serverTimezone=UTC";
    // 数据库用户名
    private static final String USERNAME = "root";
    // 数据库密码
    private static final String PASSWORD = "root";

    public static void main(String[] args) {
        testInsertBatch(100000);
    }

    /**
     * 这个方法执行了
     * 5000：插入 100000 条数据，花费：18036 :ms
     * 1000：插入 100000 条数据，花费：28063 :ms
     * 10000：插入 100000 条数据，花费：15075 :ms
     * 15000：插入 100000 条数据，花费：15144 :ms
     * @param numRows
     */
    public static void testInsertBatch(int numRows) {
        try {
            init();
            long start = System.currentTimeMillis();
            statement = conn.createStatement();
            //设置批量处理的数量
            int batchSize = 15000;
            conn.setAutoCommit(false);
            for (int i = 0; i < numRows; i++) {
                String sql = "insert into s_emp(last_name) values('insert dem0')";
                statement.addBatch(sql);
                if (i % batchSize == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                    conn.commit();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("插入 " + numRows + " 条数据，花费：" + (end - start) + " :ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 这个方法执行了好久 速度，慢的一批
     *
     * @param numRows
     */
    public static void testInsertNoBatch(int numRows) {
        try {
            init();
            long start = System.currentTimeMillis();
            statement = conn.createStatement();
            for (int i = 0; i < numRows; i++) {
                String sql = "insert into s_emp(last_name) values('insert dem0')";
                statement.execute(sql);
            }
            long end = System.currentTimeMillis();
            System.out.println("插入 " + numRows + " 条数据，花费：" + (end - start) + " :ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    public static void init() {
        // 加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 获得数据库连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void close() {
        if (null != resultSet) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (null != statement) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
